//
//  DBManager.swift
//  readtool
//
//  Created by WanJiading on 16/7/4.
//  Copyright © 2016年 DAD. All rights reserved.
//

import Foundation
import FMDB

//enum QueryOrder: String {
//    case ASC    = "ASC"         // 顺序
//    case DESC   = "DESC"        // 倒序
//}

//enum UserAudioUseType: String {
//    case Playlist   = "playlist"      // 我的歌单
//    case Sleeping   = "sleeping"      // 哄睡
//    case Download   = "download"      // 下载
//}


/// 执行更新方法回调
typealias execUpdateCompletion = (_ success: Bool, _ msg: String?) ->()

class DBManager: NSObject {

    static var dbQueue: FMDatabaseQueue! = nil
    static var db: FMDatabase? = nil
    static let pageCountDefault: Int32 = 20

	private static var dbPath: String!
	
    /// 初始化
    class func setupDatabase() {
		let docsPath: String = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true)[0]
		dbPath = URL.init(fileURLWithPath: docsPath).appendingPathComponent("ivydad.sqlite").path
		
		DebugLog("数据库路径:\(dbPath)")
		
		// 创建数据库
		self.dbQueue = FMDatabaseQueue(path: dbPath)
//		self.db = FMDatabase(path: dbPath)
//		self.db!.open()
		
		// 创建表格
		// TODO:检查友盟，用户版本数据。这3个表，等到1.3.3 以下用户没了，就可以删掉，及相关逻辑。
		// 还有160+     2017年07月31日16:45:38
//		createBookTable()
//		createAudioTable()
//		createUserAudioTable()
//		
//		createBookAudioTable()
//		createBookAudioListTable()
		
		// 我的歌单及相关表格
		PlaylistDBManager.createPlaylistTable()
		PlaylistItemDBManager.createPlaylistItemTable()
    }
	
	class func getDBQueue() -> FMDatabaseQueue {
		if dbQueue == nil {
			dbQueue = FMDatabaseQueue(path: dbPath)
		}
		return dbQueue!
	}
	
}
/**

/************************   BookAudioList   BookAudio   歌单云同步  ************************/

extension DBManager {
	
	// MARK: - 综合方法
	
	class func addBookAudioListAndBookAudios(_ bookAudioList: BookAudioList) -> Bool {
		
		var success = addBookAudioList(bookAudioList)
		if success {
			success = addBookAudios(bookAudioList)
		}
		
		return success
	}
	
	// MARK: - BookAudio
	
	/// 是否已经下载过
	class func existDownload(bookId: String, audioType: String) -> Bool {
		return getBookAudio(.download, bookId, audioType) != nil
	}
	
	/// 获取某类别下 已下载的列表
	class func getDidDownloadedBookAudios(_ listType: BookAudioListType, _ completion:@escaping (_ bookAudios: [BookAudio]) -> ()) {
		
		let sql = "select * from 't_book_audio' t1 inner join " +
					"(select * from 't_book_audio' WHERE list_type = '\(BookAudioListType.download.rawValue)' " +
					"AND member_uuid = '\(UserDefaultTool.MemberUUID)') t2 " +
					"on t1.book_id = t2.book_id AND t1.audio_type = t2.audio_type " +
					"where t1.list_type = '\(listType.rawValue)' " +
					"AND t1.member_uuid = '\(UserDefaultTool.MemberUUID)' "
		
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		let bookAudios = bindBookAudio(rs)
		completion(bookAudios)
	}
	
	class func getLastBookAudio(_ listType: BookAudioListType, _ list_id: String) -> BookAudio? {
		
		let sql = "SELECT max(order_num) as maxId, * FROM 't_book_audio' where list_type = '\(listType.rawValue)' AND list_id = '\(list_id)' AND member_uuid = '\(UserDefaultTool.MemberUUID)' order by order_num desc"
		
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		let bookAudios = bindBookAudio(rs)
		return bookAudios.first ?? nil
	}
	
	class func getBookAudios(_ listType: BookAudioListType, _ list_id: String, _ completion:@escaping (_ bookAudios: [BookAudio]) -> ()) {
		
		let sql = "SELECT * FROM 't_book_audio' where list_type = '\(listType.rawValue)' AND list_id = '\(list_id)' AND member_uuid = '\(UserDefaultTool.MemberUUID)' order by order_num asc"
		
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		let bookAudios = bindBookAudio(rs)
		completion(bookAudios)
	}
	
	class func getBookAudios(_ listType: BookAudioListType, _ completion:@escaping (_ bookAudios: [BookAudio]) -> ()) {
		
		let sql = "SELECT * FROM 't_book_audio' where list_type = '\(listType.rawValue)' AND member_uuid = '\(UserDefaultTool.MemberUUID)'  order by order_num asc"
		
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		let bookAudios = bindBookAudio(rs)
		completion(bookAudios)
	}
	
	/// 获取某音频是否下载
	class func getDidDownloadedBookAudio(_ book_id: String, _ audio_type: String) -> BookAudio? {
		return getBookAudio(.download, book_id, audio_type)
	}
	
	class func getBookAudio(_ lisType: BookAudioListType, _ book_id: String, _ audio_type: String) -> BookAudio? {
		let sql = "SELECT * FROM 't_book_audio' where list_type = '\(lisType.rawValue)' AND member_uuid = '\(UserDefaultTool.MemberUUID)' AND book_id = '\(book_id)' AND audio_type = '\(audio_type)'"
		
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		let bookAudios = bindBookAudio(rs)
		if bookAudios.count > 0 {
			return bookAudios.first
		}
		return nil
	}
	
	private class func bindBookAudio(_ rs: FMResultSet?) -> [BookAudio] {
		var bookAudios = [BookAudio]()
		if rs != nil {
			while rs!.next() {
				let bookAudio = BookAudio()
				if rs!.string(forColumn: "book_id") == nil {
					break
				}
				bookAudio.code = rs!.int(forColumn: "code")
				bookAudio.id = String(rs!.int(forColumn: "id"))
				bookAudio.order_num = String(rs!.int(forColumn: "order_num"))
				bookAudio.book_id = rs!.string(forColumn: "book_id")
				bookAudio.book_name = rs!.string(forColumn: "book_name")
				bookAudio.book_pic_url = rs!.string(forColumn: "book_pic_url")
				bookAudio.audio_type = rs!.string(forColumn: "audio_type")
				bookAudio.audio_type_name = rs!.string(forColumn: "audio_type_name")
				bookAudio.audio_url = rs!.string(forColumn: "audio_url")
				bookAudio.audio_version = rs!.string(forColumn: "audio_version")
				bookAudio.audio_length = rs!.string(forColumn: "audio_length")
				bookAudio.audio_lyric_url = rs!.string(forColumn: "audio_lyric_url")
				bookAudio.audio_lyric_version = rs!.string(forColumn: "audio_lyric_version")
				bookAudio.audio_original_url = rs!.string(forColumn: "audio_original_url")
				bookAudio.listType = BookAudioListType(rawValue: rs!.string(forColumn: "list_type"))
				bookAudio.list_id = rs!.string(forColumn: "list_id")
//				bookAudio.version = rs!.string(forColumn: "version")
				bookAudio.member_uuid = rs!.string(forColumn: "member_uuid")
//				bookAudio.is_download = rs!.int(forColumn: "is_download") == 1
				
				bookAudios.append(bookAudio)
			}
		}
		return bookAudios
	}
	
	class func getBookAudioCount(_ listType: BookAudioListType, _ completion:@escaping (_ count: Int) -> ()) {
		let sql = "SELECT count(*) as count FROM 't_book_audio' where list_type = '\(listType.rawValue)' AND member_uuid = '\(UserDefaultTool.MemberUUID)'"
		DebugLog(sql)
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		if rs != nil {
			if rs!.next() {
				let count = rs!.int(forColumn: "count")
				DebugLog("count = \(count)")
				completion(Int(count))
			}
		} else {
			completion(0)
		}
	}
	
	class func getBookAudioCount(_ listType: BookAudioListType, _ listId: String, _ completion:@escaping (_ count: Int) -> ()) {
		let sql = "SELECT count(*) as count FROM 't_book_audio' where list_type = '\(listType.rawValue)' AND list_id = '\(listId)' AND member_uuid = '\(UserDefaultTool.MemberUUID)'"
		DebugLog(sql)
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		if rs != nil {
			if rs!.next() {
				let count = rs!.int(forColumn: "count")
				completion(Int(count))
			}
		} else {
			completion(0)
		}
	}
	
	
	/// 删除某个歌单下的所有数据
	class func deleteBookAudios(_ listType: BookAudioListType, _ list_id: String) -> Bool {
		let sql = "DELETE FROM 't_book_audio' WHERE member_uuid = '\(UserDefaultTool.MemberUUID)' AND list_type = '\(listType.rawValue)' AND list_id = '\(list_id)';"
		let success = db!.executeUpdate(sql, withArgumentsIn: [])
		if !success {
			ExceptionLog("t_book_audio 删除数据出错")
		}
		
		return success
	}
	
	/// 删除某条数据
	class func deleteBookAudio(_ bookAudio: BookAudio) -> Bool {
		
		let updateSql = "UPDATE 't_book_audio' SET is_download = 0 WHERE list_id = '\(bookAudio.list_id!)' AND book_id = '\(bookAudio.book_id!)' AND audio_type = '\(bookAudio.audio_type!)' AND list_type != '\(bookAudio.listType!.rawValue)'";
		var success = db!.executeUpdate(updateSql, withArgumentsIn:[])
		if !success {
			ExceptionLog("Update t_book_audio 出错")
		}
		
		let sql = "DELETE FROM 't_book_audio' WHERE code = \(bookAudio.code);"
		DebugLog(sql)
		success = db!.executeUpdate(sql, withArgumentsIn: [])
		if !success {
			ExceptionLog("t_book_audio 删除数据出错")
		}
		
		return success
	}
	
	/// 删除已下载
	class func deleteDownload(_ listType: BookAudioListType, _ bookId: String, _ audioType: String) -> Bool {
		
		let updateSql = "DELETE FROM 't_book_audio' WHERE book_id = '\(bookId)' AND audio_type = '\(audioType)' AND list_type = '\(listType.rawValue)'";
		let success = db!.executeUpdate(updateSql, withArgumentsIn:[])
		if !success {
			ExceptionLog("Update t_book_audio 出错")
		}
		
		return success
	}
	
	/// 清空
	class func clearBookAudioAndBookAudioList() -> Bool {
		var success = false
		db!.beginTransaction()
		let deleteSql = "DELETE FROM 't_book_audio'; DELETE FROM 't_book_audio_list';"
		success = db!.executeUpdate(deleteSql, withArgumentsIn:[])
		if !success {
			ExceptionLog("clearBookAudio t_book_audio 出错")
		}
		db!.commit()
		return success
	}
	
	/// 添加数据
	class func addBookAudio(_ listType: BookAudioListType, _ bookAudio: BookAudio) -> Bool {
		
		let lastBookAudio = getLastBookAudio(listType, bookAudio.list_id!)
		let didDownloaded = getBookAudio(.download, bookAudio.book_id!, bookAudio.audio_type!) != nil

        // 游客 | 下载 需要进行本地排序
		if (UserDefaultTool.MemberUUID == "0")
            || (listType == .download) {

			if lastBookAudio != nil {
				bookAudio.order_num = String(Int(lastBookAudio!.order_num!)! + 1)
				bookAudio.id = String(Int(lastBookAudio!.id!)! + 1)
				bookAudio.list_id = lastBookAudio!.list_id
			} else {
				bookAudio.order_num = "1"
				bookAudio.id = "1"
			}
		}

		let addSql = "INSERT INTO 't_book_audio' (" +
				"id, member_uuid, list_type, list_id, " +
				"book_id, audio_type, audio_type_name, book_name, book_pic_url, audio_url, " +
				"audio_original_url, audio_length, audio_lyric_version, order_num, audio_version, audio_lyric_url, is_download)" +
				"VALUES" +
				"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"

		var success = db!.executeUpdate(addSql, withArgumentsIn: [bookAudio.id!,
		                                                          UserDefaultTool.MemberUUID,
		                                                          listType.rawValue,
		                                                          bookAudio.list_id!,
		                                                          bookAudio.book_id!,
		                                                          bookAudio.audio_type!,
		                                                          bookAudio.audio_type_name!,
		                                                          bookAudio.book_name!,
		                                                          bookAudio.book_pic_url!,
		                                                          bookAudio.audio_url!,
		                                                          bookAudio.audio_original_url!,
		                                                          bookAudio.audio_length!,
		                                                          bookAudio.audio_lyric_version ?? "",
		                                                          bookAudio.order_num ?? "-1",
		                                                          bookAudio.audio_version ?? "",
		                                                          bookAudio.audio_lyric_url!,
		                                                          didDownloaded])
		if !success {
			ExceptionLog("插入 t_book_audio 出错")
		}
		
		// 如果添加已下载，那么数据库里之前的记录都应该改成已下载
		if bookAudio.listType == .download {
			let updateSql = "UPDATE 't_book_audio' SET is_download = 1 WHERE list_id = '\(bookAudio.list_id!)' AND book_id = '\(bookAudio.book_id!)' AND audio_type = '\(bookAudio.audio_type!)' AND list_type != '\(listType.rawValue)'";
			success = db!.executeUpdate(updateSql, withArgumentsIn:[])
			if !success {
				ExceptionLog("Update t_book_audio 出错")
			}
		}
		
		return success
	}
	
	class func addBookAudios(_ bookAudioList: BookAudioList) -> Bool {
		
		if bookAudioList.book_audio_list == nil || bookAudioList.book_audio_list!.count == 0 {
			ExceptionLog("❗️bookAudioList.book_audio_list == nil || bookAudioList.book_audio_list.count == 0")
			return false
		}
		
		let bookAudios = bookAudioList.book_audio_list!
		
		if UserDefaultTool.MemberUUID == "0" {
			let lastBookAudio = getLastBookAudio(BookAudioListType(rawValue: bookAudioList.list_type!)!, bookAudioList.list_id!)
			var index = 1
			for bookAudio in bookAudios {
//				bookAudio.is_download = getDidDownloadedBookAudio(bookAudio.book_id!, bookAudio.audio_type!) != nil
				
				if lastBookAudio != nil {
					bookAudio.order_num = String(Int(lastBookAudio!.order_num!)! + index)
					bookAudio.id = String(Int(lastBookAudio!.id!)! + index)
					bookAudio.list_id = lastBookAudio!.list_id
				} else {
					bookAudio.order_num = String(index)
					bookAudio.id = String(index)
				}
				index += 1
			}
		}
		
		let addSql = "INSERT INTO 't_book_audio' (" +
			"id, member_uuid, list_type, list_id, " +
			"book_id, audio_type, audio_type_name, book_name, book_pic_url, audio_url, " +
			"audio_original_url, audio_length, audio_lyric_version, order_num, audio_version, audio_lyric_url, is_download)" +
			"VALUES" +
			"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
		
		db!.beginTransaction()
		for bookAudio in bookAudios {
			db!.executeUpdate(addSql, withArgumentsIn: [bookAudio.id!,
													  bookAudio.member_uuid ?? UserDefaultTool.MemberUUID,
													  bookAudioList.list_type!,
													  bookAudioList.list_id!,
													  bookAudio.book_id!,
													  bookAudio.audio_type!,
													  bookAudio.audio_type_name!,
													  bookAudio.book_name!,
													  bookAudio.book_pic_url!,
													  bookAudio.audio_url!,
													  bookAudio.audio_original_url ?? bookAudio.audio_url!,
													  bookAudio.audio_length!,
													  bookAudio.audio_lyric_version ?? "",
													  bookAudio.order_num ?? "-1",
													  bookAudio.audio_version ?? "",
													  bookAudio.audio_lyric_url!,
													  bookAudio.is_download])
			
		}
		
		let success = db!.commit()
		
		if !success {
			ExceptionLog("批量插入 t_book_audio 出错")
		}
		
		return success
	}
	
	/**
	更改排序
	
	- parameter sourceCode:      原.code
	- parameter sourceRownumber: 原.row_number
	- parameter targetCode:      目标.code
	- parameter targetRownumber: 目标.row_number
	*/
	class func updateBookAudioOrder(_ listType: BookAudioListType, _ sourceCode: Int, _ sourceRownumber: Int, _ targetCode: Int , _ targetRownumber: Int, completion:(_ success: Bool) -> ()) {
		var success = false
		if sourceRownumber < targetRownumber {
			var sql = "update t_book_audio set order_num = order_num -1 where order_num <= \(targetRownumber) and order_num > \(sourceRownumber) and member_uuid = '\(UserDefaultTool.MemberUUID)' AND list_type = '\(listType.rawValue)';"
			DebugLog(sql)
			success = db!.executeUpdate(sql, withArgumentsIn: [])
			if success {
				sql = "update t_book_audio set order_num = \(targetRownumber) where id = \(sourceCode) and member_uuid = '\(UserDefaultTool.MemberUUID)' AND list_type = '\(listType.rawValue)';"
				DebugLog(sql)
				success = db!.executeUpdate(sql, withArgumentsIn: [])
			}
		} else {
			var sql = "update t_book_audio set order_num = order_num + 1 where order_num < \(sourceRownumber) and order_num >= \(targetRownumber) and member_uuid = '\(UserDefaultTool.MemberUUID)' AND list_type = '\(listType.rawValue)';"
			DebugLog(sql)
			success = db!.executeUpdate(sql, withArgumentsIn: [])
			if success {
				sql = "update t_book_audio set order_num = \(targetRownumber) where id = \(sourceCode) and member_uuid = '\(UserDefaultTool.MemberUUID)' AND list_type = '\(listType.rawValue)';"
				DebugLog(sql)
				success = db!.executeUpdate(sql, withArgumentsIn: [])
			}
		}
		
		if (!success) {
			DebugLog("更改排序失败")
			completion(false)
		}
		
		completion(true)
	}
	
	
	fileprivate class func createBookAudioTable() {
		let sql = "CREATE TABLE IF NOT EXISTS 't_book_audio' (" +
			"'code' integer  PRIMARY KEY," +
			"'list_type' TEXT," +
			"'list_id' TEXT," +
			"'member_uuid' TEXT," +
			"'id' integer," +
			"'book_id' TEXT," +
			"'book_name' TEXT," +
			"'book_pic_url' TEXT," +
			"'audio_type' TEXT," +
			"'audio_type_name' TEXT," +
			"'audio_url' TEXT," +
			"'audio_original_url' TEXT," +
			"'audio_version' TEXT," +
			"'audio_length' TEXT," +
			"'audio_lyric_url' TEXT," +
			"'audio_lyric_version' TEXT," +
			"'is_download' integer," +
			"'order_num' integer);"
		
		let success = db!.executeUpdate(sql, withArgumentsIn: [])
		if !success {
			ExceptionLog("t_book 创建失败")
		}
	}
	
	
	/// 移除所有当前用户的记录，添加新的 BookAudioList 和 BookAudios
	class func removeAllAndAddBookAudioList(_ bookAudioList: BookAudioList) -> Bool {
		var success = deleteBookAudioList(bookAudioList)
		if success {
			success = deleteBookAudios(BookAudioListType(rawValue: bookAudioList.list_type!)!, bookAudioList.list_id!)
		}
		if success {
			success = addBookAudioList(bookAudioList)
		}
		if success {
			success = addBookAudios(bookAudioList)
		}
		if success {
			var useType = ""
			if bookAudioList.listType == BookAudioListType.playlist {
				useType = "playlist"
			} else if bookAudioList.listType == .download {
				useType = "download"
			}
			if useType != "" {
				success = deleteUserAudio(useType: useType)
			}
		}
		
		return success
	}

	class func getPlayListData(_ listType: BookAudioListType, useType: String) -> BookAudioList? {
		// 取出所有数据，进行分类
		let querySql = 	"select DISTINCT code,use_type,t2.*,row_number " +
			"from t_user_audio t1 " +
			"inner join" +
			"(" +
			"select t2.book_id,t2.book_name,t2.pic_url,t2.member_uuid, " +
			"t3.audio_type,t3.audio_type_name, " +
			"t3.audio_url,t3.audio_length,audio_version, " +
			"t3.audio_lyric_url,t3.audio_lyric_version " +
			"from t_book as t2 " +
			"inner join t_audio as t3 " +
			"on t2.book_id = t3.book_id " +
			"and  t2.member_uuid = '\(UserDefaultTool.MemberUUID)' " +
			") as t2 " +
			"on t1.book_id = t2.book_id " +
			"and t1.use_type = '\(useType)' " +
			"and t1.member_uuid = '\(UserDefaultTool.MemberUUID)' " +
			"and t1.audio_type = t2.audio_type " +
			"order by code asc"
		
		let rs = db!.executeQuery(querySql, withArgumentsIn: [])
		if rs != nil {
			
			var bookAudios = [BookAudio]()
			if rs != nil {
				while (rs!.next()) {
					let userAudio = bindDetailUserData(rs!)
					
					let bookAudio = BookAudio()
					bookAudio.book_id = userAudio.book?.book_id
					bookAudio.book_name = userAudio.book?.book_name
					bookAudio.audio_type = userAudio.audio_type
					bookAudio.audio_type_name = userAudio.audio_type_name
					bookAudio.member_uuid = userAudio.member_uuid
					if userAudio.use_type == "playlist" {
						bookAudio.listType = BookAudioListType.playlist
					} else if userAudio.use_type == "download" {
						bookAudio.listType = BookAudioListType.download
					}
					bookAudio.book_pic_url = userAudio.book?.book_pic_url
					
					
					bookAudio.audio_url = userAudio.audio_url
					bookAudio.audio_original_url = userAudio.audio_url
					bookAudio.audio_length = userAudio.audio_length
					bookAudio.audio_version = userAudio.audio_version
					bookAudio.audio_lyric_url = userAudio.audio_lyric_url
					bookAudio.audio_lyric_version = userAudio.audio_lyric_version
					
					bookAudio.id = "-1"
					bookAudio.list_id = "-1"
					bookAudio.order_num = "-1"
					
					bookAudios.append(bookAudio)
				}
				
				let bal = BookAudioList()
				bal.list_id = "-1"
				bal.listType = listType
				bal.book_audio_list = bookAudios
				bal.member_uuid = UserDefaultTool.MemberUUID
				bal.last_version = "-1"
				return bal
			}
		}
		return nil
	}
	
	// MARK: - BookAudioList
	
	class func getBookAudioList(_ listType: BookAudioListType) -> BookAudioList? {
		let sql = "SELECT * FROM 't_book_audio_list' WHERE member_uuid = '\(UserDefaultTool.MemberUUID)' AND list_type = '\(listType.rawValue)';"
		
		let bookAudioList: BookAudioList = BookAudioList()
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		if rs != nil {
			if rs!.next() {
				bookAudioList.code = rs!.int(forColumn: "code")
				bookAudioList.list_id = rs!.string(forColumn: "list_id")
				bookAudioList.listType = BookAudioListType(rawValue: rs!.string(forColumn: "list_type"))
				bookAudioList.last_version = rs!.string(forColumn: "last_version")
				bookAudioList.list_name = rs!.string(forColumn: "list_name")
				bookAudioList.member_uuid = rs!.string(forColumn: "member_uuid")
                bookAudioList.list_pic_url = rs!.string(forColumn: "list_pic_url")
                return bookAudioList
			}
		} else {
			ExceptionLog("【获取 BookAudioList 出错】 \(sql)")
		}
        return nil
	}
	
	class func updateBookAudioListVersion(_ bookAudioList: BookAudioList) -> Bool {
		let sql = "UPDATE 't_book_audio_list' SET last_version = '\(bookAudioList.last_version!)' WHERE member_uuid = '\(UserDefaultTool.MemberUUID)' AND list_type = '\(bookAudioList.list_type!)' AND list_id = '\(bookAudioList.list_id!)';"
		let success = db!.executeUpdate(sql, withArgumentsIn: [])
		if !success {
			ExceptionLog("【t_book_audio_list 更新数据出错】 \(sql)")
		}
		
		return success
	}
	
	class func deleteBookAudioList(_ bookAudioList: BookAudioList) -> Bool {
		let sql = "DELETE FROM 't_book_audio_list' WHERE member_uuid = '\(UserDefaultTool.MemberUUID)' AND list_type = '\(bookAudioList.list_type!)' AND list_id = '\(bookAudioList.list_id!)';"
		let success = db!.executeUpdate(sql, withArgumentsIn: [])
		if !success {
			ExceptionLog("【t_book_audio_list 删除数据出错】 \(sql)")
		}
		
		return success
	}
	
	class func addBookAudioList(_ bookAudioList: BookAudioList) -> Bool {
		let addSql = "INSERT INTO 't_book_audio_list' (member_uuid, list_type, list_id, list_name, list_pic_url, last_version)" +
			"VALUES" +
		"(?, ?, ?, ?, ?, ?);"
		
		let success = db!.executeUpdate(addSql, withArgumentsIn: [UserDefaultTool.MemberUUID, bookAudioList.list_type!, bookAudioList.list_id!, bookAudioList.list_name ?? "", bookAudioList.list_pic_url ?? "", bookAudioList.last_version ?? "-1" ])
		if !success {
			ExceptionLog("插入 t_book_audio_list 出错")
		}
		
		return success
	}
	
	fileprivate class func createBookAudioListTable() {
		let sql = "CREATE TABLE IF NOT EXISTS 't_book_audio_list' (" +
			"'code' integer  PRIMARY KEY," +
			"'member_uuid' TEXT," +
			"'list_type' TEXT," +
			"'list_id' TEXT," +
			"'list_name' TEXT," +
			"'list_pic_url' TEXT," +
	    	"'last_version' TEXT);"
		
		let success = db!.executeUpdate(sql, withArgumentsIn: [])
		if !success {
			ExceptionLog("t_book_audio_list 创建失败")
		}
	}
}


// MARK: -  我下载的\我的歌单\哄睡神器  UserAudio   ***** 不维护部分，时间久了就会被删掉

extension DBManager {
    
    /**
     获取 用户音频数量
     
     - parameter useType:    我的歌单:"playlist"   下载:"download"
     */
    class func getCount(useType: String, completion:(_ count: Int32) -> ()) {
        let sql = "select count(*) as count from t_user_audio where use_type = '\(useType)' and member_uuid = '\(UserDefaultTool.MemberUUID)'; "
        let rs = db?.executeQuery(sql, withArgumentsIn: [])
        if ((rs?.next()) != nil) {
            let count = rs!.int(forColumn: "count")
            completion(count)
        }
    }
    
    /**
     更改排序
     
     - parameter sourceCode:      原.code
     - parameter sourceRownumber: 原.row_number
     - parameter targetCode:      目标.code
     - parameter targetRownumber: 目标.row_number
     */
    class func updateOrder(_ sourceCode: Int32, sourceRownumber: Int32, targetCode: Int32 ,targetRownumber: Int32, completion:(_ success: Bool) -> ()) {
        var success = false
        if sourceRownumber < targetRownumber {
            var sql = "update t_user_audio set row_number = row_number -1 where row_number <= \(targetRownumber) and row_number > \(sourceRownumber);"
            DebugLog(sql)
            success = db!.executeUpdate(sql, withArgumentsIn: [])
            if success {
                sql = "update t_user_audio set row_number = \(targetRownumber) where code = \(sourceCode);"
                DebugLog(sql)
                success = db!.executeUpdate(sql, withArgumentsIn: [])
            }
        } else {
            var sql = "update t_user_audio set row_number = row_number + 1 where row_number < \(sourceRownumber) and row_number >= \(targetRownumber);"
            DebugLog(sql)
            success = db!.executeUpdate(sql, withArgumentsIn: [])
            if success {
                sql = "update t_user_audio set row_number = \(targetRownumber) where code = \(sourceCode);"
                DebugLog(sql)
                success = db!.executeUpdate(sql, withArgumentsIn: [])
            }
        }
        if (!success) {
            DebugLog("更改排序失败")
            completion(false)
        }

        completion(true)
    }
    
    /**
     删除 我的歌单、下载
     
     - parameter code:       code
     */
    class func deleteUserAudio(_ code: Int32?, completion:(_ success: Bool) -> ()) {
		var sql = "DELETE FROM t_user_audio WHERE member_uuid = '\(UserDefaultTool.MemberUUID)' "
		if code != nil {
			sql.append(" AND code = \(code)")
		}
        let success = db!.executeUpdate(sql, withArgumentsIn: [])
        if (!success) {
            DebugLog("删除失败")
            completion(false)
        }
        completion(true)
    }
	
	
	/// 删除某个用户的某个类别的数据
	class func deleteUserAudio(useType: String) -> Bool {
		let sql = "DELETE FROM t_user_audio WHERE member_uuid = '\(UserDefaultTool.MemberUUID)' AND use_type = '\(useType)' "
		
		let success = db!.executeUpdate(sql, withArgumentsIn: [])
		if (!success) {
			DebugLog("删除失败")
		}
		return success
	}
	
    /**
     更新我的歌单、下载     
     - parameter useType:    我的歌单:"playlist"   下载:"download"
     */
    fileprivate class func updateUserAudio(userAudio: UserAudio, useType: String, completion:(_ success: Bool) -> ()) {
        let querySql = "SELECT max(row_number) as row_number FROM t_user_audio WHERE member_uuid = '\(UserDefaultTool.MemberUUID)' AND use_type = '\(useType)'"
        let fs = db!.executeQuery(querySql, withArgumentsIn: [])
        var maxRownumber: Int32 = 0
        if ((fs?.next()) != nil) {
            maxRownumber = (fs?.int(forColumn: "row_number"))! as Int32
            maxRownumber = maxRownumber + 1
        }
        
        let sql = "INSERT INTO t_user_audio (" +
            "use_type, member_uuid, book_id, audio_type, row_number, json_path" +
            ") VALUES (" +
            "'\(useType)', ?, ?, ?, \(maxRownumber), ?);"
        let success = db!.executeUpdate(sql, withArgumentsIn: [UserDefaultTool.MemberUUID, (userAudio.book?.book_id)!, userAudio.audio_type, userAudio.jsonPath])
        if (success == true) {
            completion(true)
        } else {
            DebugLog("更新失败")
            completion(false)
        }
    }
    
    /**
     获取用户音频数据
     - parameter useType:    我的歌单:"playlist"   下载:"download"
     - parameter pageIndex:  第几页，第一页是0    -1 取所有
     - parameter pageCount:  每页的行数
     - parameter queryOrder: 顺序:"asc"  倒序:"desc"
     */
    class func getUserAudios(useType: String, pageIndex: Int32, pageCount: Int32, queryOrder: String, completion:(_ userAudios: [UserAudio]?, _ hasMore: Bool) -> ()) {
        var rowCount = pageCountDefault
        var sql =
            "select distinct code,json_path,t2.*,row_number from " +
                "t_user_audio t1 inner join (" +
                "select " +
                "t2.book_id,t2.book_name,t2.pic_url,t3.audio_type,t3.audio_type_name " +
                "from t_book as t2 inner join t_audio as t3 " +
                "on t2.book_id = t3.book_id " +
                ") as t2 " +
                "on t1.book_id = t2.book_id " +
                "and t1.use_type = '\(useType)' " +
                "and t1.member_uuid = '\(UserDefaultTool.MemberUUID)' " +
                "and t1.audio_type = t2.audio_type " +
                "order by row_number \(queryOrder) "
        
        if pageIndex > -1 {
            if pageCount > 0 {
                rowCount = pageCount
            }
            sql.append("Limit \(rowCount) offset \((pageIndex) * rowCount) ")
        }
        
		let rs = db!.executeQuery(sql, withArgumentsIn: [])
		var userAudios = [UserAudio]()
		if rs != nil {
			var currentCount: Int32 = 0;
			while (rs!.next()) {
				if currentCount == rowCount {
					currentCount = rowCount + 1
				}
				
				let userAudio = bindDetailUserData(rs!)
				userAudios.append(userAudio)
				
				currentCount = currentCount + 1
			}
			completion(userAudios, (currentCount == rowCount + 1) ? true : false )
		} else {
			completion(userAudios, false )
		}
    }
    
    fileprivate class func bindDetailUserData(_ resultSet: FMResultSet) -> UserAudio {
        let userAudio = UserAudio()
        let book = Book()
        book.book_id = resultSet.string(forColumn: "book_id")
        book.book_name = resultSet.string(forColumn: "book_name")
        book.book_pic_url = resultSet.string(forColumn: "pic_url")
        
        userAudio.book = book
        userAudio.audio_type = resultSet.string(forColumn: "audio_type")
        if resultSet.columnIndex(forName: "audio_type_name") > -1 {
            userAudio.audio_type_name = resultSet.string(forColumn: "audio_type_name")
        }
        
        userAudio.rowNumber = resultSet.int(forColumn: "row_number")
        userAudio.code = resultSet.int(forColumn: "code")
//        userAudio.jsonPath = resultSet.string(forColumn: "json_path")
		userAudio.member_uuid = resultSet.string(forColumn: "member_uuid")
		userAudio.use_type = resultSet.string(forColumn: "use_type")
		
		userAudio.audio_url = resultSet.string(forColumn: "audio_url")
		userAudio.audio_length = resultSet.string(forColumn: "audio_length")
		userAudio.audio_version = resultSet.string(forColumn: "audio_version")
		userAudio.audio_lyric_url = resultSet.string(forColumn: "audio_lyric_url")
		userAudio.audio_lyric_version = resultSet.string(forColumn: "audio_lyric_version")
		
		
		
        return userAudio
    }

    
    /**
     获取用户音频数据
     - parameter useType:    我的歌单:"playlist"   下载:"download"
     - parameter pageIndex:  第几页，第一页是0
     - parameter queryOrder: 顺序:"asc"  倒序:"desc"
     */
//    class func getUserAudios(useType useType: String, pageIndex: Int32, queryOrder: String, completion:(userAudios: [UserAudio]?, hasMore: Bool) -> ()) {
//        getUserAudios(useType: useType, pageIndex: pageIndex, pageCount: pageCountDefault, queryOrder: queryOrder) { (userAudios, hasMore) in
//            completion(userAudios: userAudios, hasMore: hasMore)
//        }
//    }
    
    /**
     获取所有的 用户音频数据
     - parameter useType:    我的歌单:"playlist"   下载:"download"
     - parameter queryOrder: 顺序:"asc"  倒序:"desc"
     */
    class func getUserAudios(useType: String, queryOrder: String, completion:@escaping (_ userAudios: [UserAudio]?) -> ()) {
        getUserAudios(useType: useType, pageIndex: -1, pageCount: pageCountDefault, queryOrder: queryOrder) { (userAudios, hasMore) in
            completion(userAudios)
        }
    }
    
    class func getUserAudio(useType: String, bookId: String, audioType: String, completion:(_ exist: Bool) -> ()) {
        let sql =
            "select count(*) as count from t_user_audio " +
            "where member_uuid = '\(UserDefaultTool.MemberUUID)' " +
            "and book_id = \(bookId) " +
            "and audio_type = '\(audioType)' " +
            "and use_type = '\(useType)' "
       
        var exist = false
        let rs = db!.executeQuery(sql, withArgumentsIn: [])
        if rs != nil {
            if (rs!.next()) {
                let count = rs?.int(forColumn: "count")
                exist = count! > 0 ? true : false
            } 
        }
        completion(exist)
    }
	
    
    /// 关联到我的 音频表
    fileprivate class func createUserAudioTable() {
        let sql = "CREATE TABLE IF NOT EXISTS 't_user_audio' (" +
            "'code' INTEGER PRIMARY KEY AUTOINCREMENT," +
            "'use_type' TEXT,"   +         // 用于哪个功能, 目前有 playlist 。 应该是 use_type
            "'member_uuid' TEXT," +
            "'book_id' INTEGER," +
            "'audio_type' TEXT," +
            "'json_path' TEXT," +
            "'row_number' INTEGER);"
  
        let success = db!.executeUpdate(sql, withArgumentsIn: [])
        if !success {
            DebugLog("t_book 创建失败")
        }
    }
}

/************************  Audio  ************************/

extension DBManager {
	

    class func updateAudios(_ audios: [Audio], book_id: String, completion:(_ success: Bool) -> ()) {
		
//            let deleteSuceess = db!.executeUpdate("DELETE FROM t_audio WHERE book_id = \(book_id)", withArgumentsInArray: [])
//            if (!deleteSuceess) {
//                DebugLog("删除失败")
//                completion(success: false)
//                return
//            }

        let count = audios.count
        for i in 0..<count {
            let audio = audios[i]
            let updateSQL = "REPLACE INSERT INTO 't_audio' (" +
            "book_id, audio_type, audio_type_name, audio_url, audio_version, audio_length, audio_lyric_url, audio_lyric_version)" +
            "VALUES" +
            "(?,?,?,?,?,?,?,?)"
            let updateSuccess = db!.executeUpdate(updateSQL, withArgumentsIn: [book_id, audio.audio_type!, audio.audio_type_name!, audio.audio_original_url!, audio.audio_version!, audio.audio_length!, audio.audio_lyric_url!, audio.audio_lyric_version!])
            if (!updateSuccess) {
                DebugLog("更新失败")
                completion(false)
                return
            }
        }

        completion(true)
        
    }

    class func getAudios(_ bookId: String, completion:(_ audios: [Audio]?) -> ()) {
        let sql = "SELECT * FROM t_audio WHERE book_id = \(bookId)"
        var audios = [Audio]()
        let rs = db!.executeQuery(sql, withArgumentsIn: [])
        if ((rs?.next()) != nil) {
            let audio = Audio()
            audio.audio_type = rs!.string(forColumn: "audio_type")
            audio.audio_type_name = rs!.string(forColumn: "audio_type_name")
            audio.audio_original_url = rs!.string(forColumn: "audio_url")
            audio.audio_version = rs!.string(forColumn: "audio_version")
            audio.audio_length = rs!.string(forColumn: "audio_length")
            audio.audio_lyric_url = rs!.string(forColumn: "audio_lyric_url")
            audio.audio_lyric_version = rs!.string(forColumn: "audio_lyric_version")
            audios.append(audio)
        }
        completion(audios)
    }

    fileprivate class func createAudioTable() {
        let sql = "CREATE TABLE IF NOT EXISTS 't_audio' (" +
            "'book_id' INTEGER NOT NULL," +
            "'audio_type' TEXT," +
            "'audio_type_name' TEXT," +
            "'audio_url' TEXT," +
            "'audio_version' TEXT," +
            "'audio_length' TEXT," +
            "'audio_lyric_url' TEXT," +
            "'audio_lyric_version' TEXT," +
            "PRIMARY KEY ('book_id', 'audio_type'));"

        let success = db!.executeUpdate(sql, withArgumentsIn: [])
        if !success {
            DebugLog("t_book 创建失败")
        }
    }
}

/************************  Book  ************************/

extension DBManager {
    
    /**
     更新book 及 对应的 audio
     
     - parameter book:       book模型，必须包含 audio_list，否则无法更新 audio 信息
     */
    fileprivate class func updateBookAndAudio(book: Book, completion:(_ success: Bool) -> ()) {
        let sql = "REPLACE INTO t_book (" +
            "book_id, member_uuid, book_name, pic_url)" +
            "VALUES" +
        "(?, ?, ?, ?)"
		
		var isError = false
		if book.book_id == nil || book.book_name == nil || book.book_pic_url == nil {
			isError =  true
		}
		if isError {
			FabricTool.setCustomInfo("updateBookAndAudio book.book_id", value: "nil")
			FabricTool.setCustomInfo("updateBookAndAudio book.book_name", value: "nil")
			FabricTool.setCustomInfo("updateBookAndAudio book.book_pic_url", value: "nil")
			return
		}
		
        let success = db!.executeUpdate(sql, withArgumentsIn: [book.book_id!, UserDefaultTool.MemberUUID, book.book_name!, book.book_pic_url!])
        if success {
            let count = book.audio_list!.count
            for i in 0..<count {
                let audio = book.audio_list![i]
                let updateSQL = "REPLACE INTO 't_audio' (" +
                    "book_id, audio_type, audio_type_name, audio_url, audio_version, audio_length, audio_lyric_url, audio_lyric_version)" +
                    "VALUES" +
                "(?,?,?,?,?,?,?,?)"
                let updateSuccess = db!.executeUpdate(updateSQL, withArgumentsIn: [book.book_id!, audio.audio_type!, audio.audio_type_name!, audio.audio_original_url!, audio.audio_version!, audio.audio_length!, audio.audio_lyric_url!, audio.audio_lyric_version!])
                if (!updateSuccess) {
                    DebugLog("更新失败")
                    completion(false)
                    return
                }
            }
            completion(true)
        } else {
            DebugLog("更新Book 失败 \([book.book_id, UserDefaultTool.MemberUUID, book.book_name, book.book_pic_url])")
        }
    }
    
    fileprivate class func createBookTable() {
        let sql = "CREATE TABLE IF NOT EXISTS 't_book' (" +
            "'book_id' INTEGER NOT NULL," +
            "'member_uuid' TEXT NOT NULL," +
            "'book_name' TEXT," +
            "'pic_url' TEXT," +
        "PRIMARY KEY ('book_id', 'member_uuid'));"
        
        let success = db!.executeUpdate(sql, withArgumentsIn: [])
        if !success {
            DebugLog("t_book 创建失败")
        }
    }
    
    /**
     更新 book 信息，不包含 audio_list
     */
//    private class func updateBook(book: Book, comletion:(success: Bool) -> ()) {
//        let sql = "REPLACE INTO t_book (" +
//            "book_id, member_uuid, book_name, pic_url)" +
//            "VALUES" +
//        "(?, ?, ?, ?)"
//        let success = db!.executeUpdate(sql, withArgumentsInArray: [book.book_id!, UserDefaultTool.MemberUUID, book.book_name!, book.book_pic_url!])
//        if success {
//            comletion(success: success)
//        } else {
//            DebugLog("更新Book 失败 \([book.book_id, UserDefaultTool.MemberUUID, book.book_name, book.book_pic_url])")
//        }
//    }

    
//    class func getBook(bookId: String, completion:(book: Book?) -> ()) {
//        let sql = "SELECT * FROM t_book WHERE book_id = \(bookId) AND member_uuid = '\(UserDefaultTool.MemberUUID)'"
//        
//        let result = db!.executeQuery(sql, withArgumentsInArray: [])
//        if ((result?.next()) != nil) {
//            let book = bindBook(result!)
//            completion(book: book)
//        } else {
//            DebugLog("获取Book 失败 \(sql)")
//        }
//    }

//    private class func bindBook(rs: FMResultSet) -> Book {
//        let book = Book()
//        book.book_id = String(rs.intForColumn("book_id"))
//        book.book_name = rs.stringForColumn("book_name")
//        book.book_pic_url = rs.stringForColumn("pic_url")
//        return book
//    }
}


/************************  测试sql语句  ************************/

/**
 
 select * from t_audio


 CREATE TABLE IF NOT EXISTS t_audio (
 book_id INTEGER NOT NULL,
 audio_type TEXT,
 audio_type_name TEXT,
 audio_url TEXT,
 audio_version TEXT,
 audio_length TEXT,
 audio_lyric_url TEXT,
 audio_lyric_version TEXT,
 PRIMARY KEY (book_id, audio_type));


 CREATE TABLE IF NOT EXISTS t_book (
 book_id INTEGER NOT NULL,
 member_uuid INTEGER NOT NULL,
 book_name TEXT,
 pic_url TEXT,
 PRIMARY KEY (book_id, member_uuid));

 
 CREATE TABLE IF NOT EXISTS t_user_audio (
 code INTEGER PRIMARY KEY AUTOINCREMENT,
 use_type TEXT,
 member_uuid INTEGER,
 book_id INTEGER,
 audio_type TEXT,
 row_number INTEGER);
 
 
 
 
 select code,json_path,t2.*,row_number from t_user_audio t1 inner join
 (
 select t2.book_id,t2.book_name,t2.pic_url,t3.audio_type,t3.audio_type_name
 from t_book as t2 inner join t_audio as t3
 on t2.book_id = t3.book_id
 ) as t2
 on t1.book_id = t2.book_id
 and t1.use_type = 'playlist'
 and t1.member_uuid = '0'
 and t1.audio_type = t2.audio_type
 order by code asc
 Limit 20 offset 0

 
 select max(row_number) as row_number from t_user_audio where member_uuid = 364 and use_type = 'playlist'


 insert into t_user_audio (
 use_type, member_uuid, book_id, book_name, audio_type, audio_type_name, row_number
 ) values (
 'download', 364, 1, 'Dad Mum war', 'http://kfas;dlkkl;fas.png', 'music', '歌曲', 1
 )


 */
*/
